﻿using Entity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBService.DataHelper
{
   public class DbHelper
    {
       static string ConnectionString =ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        /// <summary>     
        /// 执行查询语句，返回查询结果的DataSet   
        /// </summary>     
        /// <param name="sql">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>  
        public DataSet ExecuteDataSet(string cmdText, List<SqlParameter> parameters)
        {
            return ExecuteDataSet(cmdText, parameters, CommandType.Text);
        }
        /// <summary>     
        /// 执行查询语句，返回查询结果的DataSet
        /// </summary>     
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public DataSet ExecuteDataSet(string cmdText, IList<SqlParameter> parameters, CommandType commandType)
        {
            using (SqlCommand command = CreateSqlCommand(cmdText, parameters, commandType))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    command.CommandText = cmdText;
                    adapter.SelectCommand = command;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    command.Parameters.Clear();//清空参数集合
                    return ds;
                }
            }
        }
        /// <summary>     
        /// 执行查询语句，返回查询结果的DataTable   
        /// </summary>     
        /// <param name="sql">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>  
        public DataTable ExecuteDataTable(string cmdText, List<SqlParameter> parameters)
        {
            if (cmdText == null || cmdText.Trim().Equals(""))
            {
                return null;
            }
            var ds=this.ExecuteDataSet(cmdText, parameters, CommandType.Text);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        #region 多表事物更新方法(仅做添加、删除、修改使用)
        /// <summary>
        /// 多表事物更新方法(仅做添加、删除、修改使用)
        /// </summary>
        /// <param name="cmdlist">执行SQL语句</param>
        /// <param name="parlist">参数集合</param>
        /// <returns></returns>
        public ReturnResult TransUpdate(List<string> cmdlist, List<SqlParameter[]> parlist)
        {
            ReturnResult rsl = new ReturnResult();
            try
            {
                using (SqlConnection connection = new SqlConnection())
                {
                    connection.ConnectionString = ConnectionString;
                    connection.Open();
                    using (SqlTransaction myTran = connection.BeginTransaction())//创建事物
                    {
                        try
                        {
                            SqlCommand command = new SqlCommand();
                            command.Transaction = myTran;
                            command.Connection = connection;
                            for (int i = 0; i < cmdlist.Count; i++)
                            {
                                command.CommandType = CommandType.Text;
                                command.CommandText = cmdlist[i].ToString();
                                command.Parameters.Clear();//清空参数集合
                                if (parlist != null)
                                {
                                    foreach (SqlParameter parameter in parlist[i])
                                    {//参数赋值
                                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                                        {
                                            parameter.Value = DBNull.Value;
                                        }
                                        command.Parameters.Add(parameter);
                                    }
                                }
                         
                                command.ExecuteNonQuery();
                            }
                            myTran.Commit();
                            rsl.Result = true;
                        }
                        catch (Exception ex)
                        {
                            rsl.Message = ex.Message;
                            myTran.Rollback();
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        #endregion

       /// <summary>
        /// 对数据库执行增删改操作，返回受影响的行数
       /// </summary>
       /// <param name="sql">执行的sql</param>
       /// <param name="parameters">参数集合集</param>
       /// <returns></returns>
        public ReturnResult ExecuteNonQuery(string sql, IList<SqlParameter> parameters)
        {
            return ExecuteNonQuery(sql, parameters, CommandType.Text);
        }
        /// <summary>     
        /// 对数据库执行增删改操作，返回受影响的行数。     
        /// </summary>     
        /// <param name="sql">要执行的增删改的SQL语句</param>     
        /// <param name="parameters">执行增删改语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public ReturnResult ExecuteNonQuery(string sql, IList<SqlParameter> parameters, CommandType commandType)
        {
            ReturnResult rsl = new ReturnResult();
            try
            {
                using (SqlCommand command = CreateSqlCommand(sql, parameters, commandType))
                {
                    command.Connection.Open();
                    int affectedRows = command.ExecuteNonQuery();
                    command.Connection.Close();
                    command.Parameters.Clear();//清空参数集合
                    if (affectedRows > 0)
                    {
                        rsl.Result = true;
                        rsl.Message = "操作成功！";
                    }
                    else
                    {
                        rsl.Result = false;
                        rsl.Message = "数据没有更新成功！";
                    }
                }
            }
            catch (Exception ex)
            {
                rsl.Result = false;
                rsl.Message = ex.Message;
            }
            return rsl;
        }  

        private SqlCommand CreateSqlCommand(string cmdText, IList<SqlParameter> parameters, CommandType commandType)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Connection = connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
          #region 分页数据查询
    /// <summary>
    /// 分页数据查询
    /// </summary>
    /// <param name="count">信息总数</param>
    /// <param name="cmdParms">参数集</param>
    /// <returns></returns>
    public DataTable PageList(ref string count, SqlParameter[] cmdParms)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConnectionString;
        SqlCommand command = new SqlCommand();
        using (connection)
        {
            // 设置sql连接
            command.Connection = connection;
            string proName = ConfigurationManager.AppSettings["PageProcedure"].ToString();
            command.CommandText = proName;   // 分页存储过程名
            command.CommandType = CommandType.StoredProcedure;// 指定执行语句为存储过程
            command.Parameters.AddRange(cmdParms);
            SqlParameter output = command.Parameters[6];
            output.Direction = ParameterDirection.Output;
            using (SqlDataAdapter adapter =new SqlDataAdapter())
            {
                adapter.SelectCommand = command;
                DataSet ds = new DataSet();  
                adapter.Fill(ds);// 填充dataset
                command.Parameters.Clear();//清空参数集合
                count = output.Value.ToString();
                return ds.Tables[0];
            }

        }
    }
    #endregion
    }
}
